
********************************************************************************
********************************************************************************
** get community test rates  
********************************************************************************
********************************************************************************
use health/test_person_level, clear
merge m:1 sid using health/demographics, keep(1 3) assert(2 3) nogen
gen county_code = floor(geocode_census_block_2010 /1e10)

gen ever_tested = 1
collapse (sum) ever_tested, by(county_code)
drop if missing(county_code) | county_code == 0

merge m:1 county_code using data/county_pop, assert(3)  nogen force
gen county_test_rate = ever_test/totpop


gen c0 = ""
gen c1 = ""

local row = 0 
foreach stat in mean p25 p75 {
	local replace "" 
	if "`stat'" == "mean" local name "Average"
	if "`stat'" == "p25" local name "25th percentile"
	if "`stat'" == "p75" local name "75th percentile"
	
	sum county_test_rate, d

	local ++row
	replace c0 = "`name' &"  in `row'
	replace c1 = string(`=r(`stat')', "%5.3f") + " \\" in `row'
	
	
}


list c0 c1 in 1/`row', noobs clean
outsheet c0 c1 in 1/`row' using tables/county_level_rates.tex, replace ///
	noquote nonames delimit(" ")

	
save health/county_test_rate, replace 



********************************************************************************
********************************************************************************
** compare test rates for hospitalized people to general pop
********************************************************************************
********************************************************************************

** individual level 
use health/county_test_rate, clear 
sum county_test_rate [fw=totpop], d
local avg_person = r(mean)
scalarout using "scratch/county_ind_test_rates.csv", ///
	id("Average person") num(`avg_person') fmt(%5.3f) replace
	
	
foreach group in no_icli any_major any_icli{
	
	if "`group'" == "no_icli" local gn "Non-ICLI"
	if "`group'" == "any_major" local gn "Clear cause"
	if "`group'" == "any_icli" local gn "ICLI"
	
	use health/inp_clean, clear
	if "`group'" == "no_icli" gen no_icli = any_icli == 0 & ~no_dx
	keep if `group' == 1
	
	merge m:1 sid using health/demographics, keep(1 3) assert(2 3) nogen 
	gen county_code = floor(geocode_census_block_2010 /1e10)

	drop if missing(age_wide) | age_wide == 0
	drop if missing(county_code) | county_code == 0
	merge m:1 age_wide using data/age_wide_counts, keep(1 3) assert(3) nogen
	bysort age_wide: gen nt = _N
	by age_wide: gen tag_aw = _n==1
	sum n if tag_aw
	local pop = r(sum)
	gen wt = (n/`pop') / (nt/_N)
	
	
	merge m:1 county_code using health/county_test_rate, force
	sum county_test_rate  [aw=wt]
	scalarout using "scratch/county_ind_test_rates.csv", ///
		id("`gn' ") num(`=r(mean)') fmt(%5.3f)
		
	local t= abs(r(mean)-`avg_person') /(r(sd)/sqrt(r(N)))
	di `t'

	scalarout using "scratch/county_ind_test_rates.csv", ///
		id(" ") num(`t') fmt(%5.1f)
		
}




********************************************************************************
********************************************************************************
** clean up county test rate scratch work
********************************************************************************
********************************************************************************

import delimited using scratch/county_ind_test_rates.csv, clear
replace v1 = "\underline{Population} \\ " + v1  in 1
replace v1 = v1 + " & "  
gen v3 = "\\"
replace v3 = v3 + " \\ \underline{Hospitalizations} \\" in 1
tostring v2, replace
replace v2 = "[" + v2 + "]" if _n == 3 | _n == 5 | _n == 7

outsheet v1 v2 v3 using tables/county_ind_test_rates.tex, replace ///
	noquote nonames delimit(" ")

	list , noobs clean
